﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;
using System.Collections;
using System.Reflection;

namespace Common.SQL
{
    /// <summary>
    /// 数据库操作类
    /// 作者：wgy
    /// 日期：2011.07.09
    /// </summary>
    public class SQLHelper
    {
        static log4net.ILog logger = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        /// <summary>
        /// 无参数的sql语句
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int Execute(IDbConnection connection, string sql)
        {
            return Execute(connection, CommandType.Text, sql, null);
        }

        /// <summary>
        /// 有参数的sql语句
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static int Execute(IDbConnection connection, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {
            logger.Debug(cmdText);

            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, (MySqlConnection)connection, null, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 执行带事务的命令
        /// </summary>
        /// <param name="trans"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static int Execute(MySqlTransaction trans, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {
            logger.Debug(cmdText);

            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        public static object ExecuteScalar(IDbConnection connection, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {
            logger.Debug("ExecuteScalar: " + cmdText);

            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, (MySqlConnection)connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        /// 增加此方法的事务查询
        /// </summary>
        /// <param name="trans"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static object ExecuteScalarTransaction(IDbTransaction trans, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters) 
        {
            logger.Debug(cmdText);

            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, (MySqlConnection)trans.Connection, trans, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }
        /// <summary>
        /// 事务执行多条SQL语句
        /// </summary>
        /// <param name="Con"></param>
        /// <param name="SQLStringList"></param>
        public static void ExecuteSqlTran(IDbTransaction trans, Hashtable SQLStringList)
        {
            MySqlCommand cmd = new MySqlCommand();
            foreach (DictionaryEntry myDE in SQLStringList)
            {
                string cmdText = myDE.Key.ToString();
                MySqlParameter[] cmdParms = (MySqlParameter[])myDE.Value;
                logger.Debug(cmdText);
                PrepareCommand(cmd, (MySqlConnection)trans.Connection, trans, CommandType.Text, cmdText, cmdParms);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
        }

        public static int ExecuteTransaction(IDbTransaction trans, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {
            logger.Debug(cmdText);

            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, (MySqlConnection)trans.Connection, trans, cmdType, cmdText, commandParameters);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        public static IDataReader QueryReader(IDbConnection connection, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {
            logger.Debug(cmdText);

            MySqlCommand cmd = new MySqlCommand();

            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            PrepareCommand(cmd, (MySqlConnection)connection, null, cmdType, cmdText, commandParameters);
            //MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            MySqlDataReader rdr = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            return rdr;
        }

        public static IDataReader QueryReaderTran(IDbTransaction trans, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {
            logger.Debug(cmdText);

            MySqlCommand cmd = new MySqlCommand();

            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            PrepareCommand(cmd, (MySqlConnection)trans.Connection, trans, cmdType, cmdText, commandParameters);
            MySqlDataReader rdr = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            return rdr;
        }

        public static DataSet QueryDataSet(IDbConnection connection, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {
            logger.Debug(cmdText);

            MySqlCommand cmd = new MySqlCommand();

            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            PrepareCommand(cmd,(MySqlConnection)connection,null,cmdType,cmdText,commandParameters);
            DataSet result=new DataSet();
            try
            {
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                adapter.Fill(result);
                return result;
            }
            catch
            {
                connection.Close();
                throw;
            }
        }

        public static int UpdateDataTable(DataTable DT, IDbConnection connection, CommandType cmdType, string cmdText, params IDbDataParameter[] commandParameters)
        {
            logger.Debug(cmdText);

            MySqlCommand cmd = new MySqlCommand();

            // we use a try/catch here because if the method throws an exception we want to 
            // close the connection throw code, because no datareader will exist, hence the 
            // commandBehaviour.CloseConnection will not work
            PrepareCommand(cmd, (MySqlConnection)connection, null, cmdType, cmdText, commandParameters);
            DataSet result = new DataSet();
            try
            {
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                MySqlCommandBuilder sc = new MySqlCommandBuilder((MySqlDataAdapter)adapter);
                adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                adapter.UpdateCommand = sc.GetUpdateCommand();
                adapter.InsertCommand = sc.GetInsertCommand();
                adapter.DeleteCommand = sc.GetDeleteCommand();

                return adapter.Update(DT);
            }
            catch
            {
                connection.Close();
                throw;
            }
        }

        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, IDbTransaction trans, CommandType cmdType, string cmdText, IDbDataParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = (MySqlTransaction)trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        private static System.Collections.Hashtable parmCache = System.Collections.Hashtable.Synchronized(new System.Collections.Hashtable());

        public static void CacheParameters(string cacheKey, params IDbDataParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        public static IDbDataParameter[] GetCachedParameters(string cacheKey)
        {
            MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            //复制对象，防止多线程同时获取的情况下，出现脏数据
            MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>
        /// 模糊查询中特殊字符的过滤，使用时过滤一下条件
        /// </summary>
        /// <param name="sqlcondition"></param>
        /// <returns></returns>
        public static string GetLikeCondition(string sqlcondition)
        {
            if (string.IsNullOrEmpty(sqlcondition))
                return null;

            sqlcondition = sqlcondition.Replace("\\", "\\\\\\\\");
            sqlcondition = sqlcondition.Replace("_", @"\_");
            sqlcondition = sqlcondition.Replace("%", "\\%");
            sqlcondition = sqlcondition.Replace("'", "\\'");
            return sqlcondition;
        }
    }
}
